package jdbc;

import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.sql.*;
import java.time.Duration;
import java.time.Instant;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * Created with IntelliJ IDEA.
 * Description:
 *
 * @author lzy
 * @create 2018-07-30 上午10:12
 * <p>
 * JDBC是一个接口，各个数据库厂商必须实现该接口
 */
public class JDBCTest {

    private static final Logger LOGGER = LoggerFactory.getLogger(JDBCTest.class);

    /**
     * ResultSetMetaData是描述ResultSet的元数据对象，即从中可以得到
     * 结果集有多少列，每列的列名是什么
     * 通过ResultSet的getMetaData()得到：
     * 1. int getColumnCount():SQL语句中包含那些列
     * 2. String getColumnLabel(int column):获取指定的列的别名，其中索引从1开始
     */
    @Test
    public void testResultSetMetaData() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "SELECT id, name, email, birth FROM customers WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, 2);
            // 先利用SQL进行查询，得到结果集
            resultSet = preparedStatement.executeQuery();

            Map<String, Object> values = new HashMap<>();

            // 得到ResultSetMetaData对象
            ResultSetMetaData metaData = resultSet.getMetaData();

            while (resultSet.next()) {
                // 打印每一列的列名
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(columnLabel);
                    LOGGER.debug(columnLabel);
                    values.put(columnLabel, columnValue);
                }
            }
            LOGGER.debug(values.toString());

            Class clazz = Customer.class;
            Object object = clazz.newInstance();
            for (Map.Entry<String, Object> entry : values.entrySet()) {
                String fieldName = entry.getKey();
                Object fieldValue = entry.getValue();
                Field field = clazz.getDeclaredField(fieldName);
                field.setAccessible(true);
                field.set(object, fieldValue);
            }

            LOGGER.debug(object.toString());

        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }

    @Test
    public void testGet() {
        String sql = "SELECT id, name, email, birth FROM customers WHERE id=?";
        Customer customer = get(sql, Customer.class, 1);
        LOGGER.debug(customer.toString());
    }

    public <T> T get(String sql, Class<T> clazz, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        T result = null;

        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 1. 先利用SQL进行查询，得到结果集
            resultSet = preparedStatement.executeQuery();
            // 2. 得到ResultSetMateData对象
            ResultSetMetaData rsmd = resultSet.getMetaData();
            // 3. 创建一个Map<String, Object>对象，键：SQL查询的列的别名，值：列的值
            Map<String, Object> values = new HashMap<>();
            // 4. 获取结果集每一行的值，填充3对应的map
            if (resultSet.next()) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    String columnLabel = rsmd.getColumnLabel(i);
                    Object columnValue = resultSet.getObject(columnLabel);
                    values.put(columnLabel, columnValue);
                }
            } else {
                return null;
            }
            // 5. 若map不为空，利用反射创建实体类的对象
            if (values.isEmpty()) {
                return null;
            }
            result = clazz.newInstance();
            // 6. 遍历map利用反射为实体类赋值
            for (Map.Entry<String, Object> entry : values.entrySet()) {
                Field field = clazz.getDeclaredField(entry.getKey());
                field.setAccessible(true);
                field.set(result, entry.getValue());
            }
        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
        return result;
    }

    /**
     * SQL注入是利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中
     * 注入非法SQL语句段或命令，从而利用系统的SQL引擎完成恶意的做法
     */
    @Test
    public void testSQLInjection() {
        String userName = "admin";
        String password = "a' or '1=1";
        String sql = "SELECT * FROM users WHERE username='" + userName + "' AND password='" + password + "'";

        LOGGER.debug(sql);

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                LOGGER.info("登录成功");
            } else {
                LOGGER.info("用户密码不匹配或用户名不存在！");
            }
        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }

    @Test
    public void testPrepareStatement() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO customers (name, email, birth) VALUES(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "test1");
            preparedStatement.setString(2, "test1@test.com");
            preparedStatement.setString(3, "1988-01-02");
            int result = preparedStatement.executeUpdate();
            LOGGER.debug("is successful?:" + (result > 0));
        } catch (Exception e) {
            LOGGER.error("error", e);
        } finally {
            JdbcUtils.release(connection, preparedStatement);
        }
    }

    /**
     * ResultSet：结果集，封装了使用JDBC进行查询的结果
     * 1. 调用Statement对象的executeQuery(sql)可以得到结果集
     * 2. ResultSet：返回的实际上就是一张数据表,有一个指针，指向数据表的第一行的前面
     * 可以调用next()方法检测下一行是否有效，若有效，该方法返回true，且指针下移
     * 相当于Iterator对象的hasNext和next方法的结合体
     * 3. 当指针对位到第一行时，可以通过getXXX(index)或者getXXX（columnName）获取每一列的值
     * 例如：getInt(1),getString("name")
     * 注意：resultSet列起始索引从1开始，而不是从0开始
     * 4. ResultSet也需要进行关闭。
     */
    @Test
    public void testResultSet() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            //获取数据库customers表第一条记录并打印
            //1. 获取Connection
            connection = JdbcUtils.getConnection();
            //2. 获取Statement
            statement = connection.createStatement();
            //3. 准备SQL
            String sql = "SELECT id, name, email, birth FROM customers";
            //4. 执行查询
            resultSet = statement.executeQuery(sql);
            //5. 处理ResultSet
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString("name");
                String email = resultSet.getString(3);
                String birth = resultSet.getString(4);

                LOGGER.debug(String.valueOf(id));
                LOGGER.debug(name);
                LOGGER.debug(email);
                LOGGER.debug(birth);
            }
        } catch (Exception e) {
            LOGGER.error("query error", e);
        } finally {
            //6.关闭数据库连接
            JdbcUtils.release(connection, statement, resultSet);
        }
    }

    /**
     * 通用的更新方法：包括INSERT，UPDATE，DELETE
     */
    public void update(String sql) {
        Connection connection = null;
        Statement statement = null;

        try {
            // 1.获取数据库链接
            connection = JdbcUtils.getConnection();
            // 3.执行插入
            // 3.1 获取操作sql语句的Statement对象：
            // 调用Connection的createStatement()方法
            statement = connection.createStatement();

            // 3.2 调用Statement对象的executeUpdate(sql)执行sql语句插入
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            LOGGER.error("execute sql error", e);
        } finally {
            // 4.关闭Statement对象
            JdbcUtils.release(connection, statement);
        }
    }

    /**
     * 通过JDBC向数据库中插入数据
     * 1. Statement：用于执行sql语句的对象
     * i.通过Connection的createStatement()方法来获取
     * ii.通过executeUpdate(sql)可以执行sql语句
     * iii.传入SQL语句可以是INSERT，UPDATE，DELETE
     * <p>
     * 2. Connection,Statement都是应用程序和数据库服务器的链接资源，使用后一定要关闭
     * 需要在finally中关闭Connection和Statement对象
     * <p>
     * 3. 关闭的顺序是先关闭Statement,后关闭Connection
     */
    @Test
    public void testStatement() throws SQLException, IOException, ClassNotFoundException {

        Connection connection = null;
        Statement statement = null;

        try {
            // 1.获取数据库链接
            connection = getConnectionByManager();
            // 2.准备插入的SQL语句
//            String sql = "INSERT INTO customers (name,email,birth) VALUES('aa','aa@test.com','1989-04-04')";
            String sql = "UPDATE customers SET name='aaa', email='aaa@test.com', birth='1589-04-04' WHERE id=2";
//            String sql = "DELETE FROM customers WHERE id=2";
            // 3.执行插入
            // 3.1 获取操作sql语句的Statement对象：
            // 调用Connection的createStatement()方法
            statement = connection.createStatement();

            // 3.2 调用Statement对象的executeUpdate(sql)执行sql语句插入
            statement.executeUpdate(sql);
        } finally {
            // 4.关闭Statement对象
            if (statement != null) {
                statement.close();
            }
            // 5.关闭链接
            if (connection != null) {
                connection.close();
            }
        }
    }

    /**
     * DriverManager是驱动管理类
     * 1.可以通过重载的getConnection()方法获取数据库链接，较为方便
     * 2.可以同时管理多个驱动程序：若注册了多个数据库链接，则调用getConnection()
     * 方法时传入的参数不同，即返回不同的数据库链接
     *
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void testDriverMananger() throws IOException, ClassNotFoundException, SQLException {
        // 1.准备链接数据库的4个字符串
        InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties property = new Properties();
        property.load(in);
        String driverClass = property.getProperty("db.driver");
        String url = property.getProperty("db.url");
        String user = property.getProperty("user");
        String password = property.getProperty("db.password");

        // 2.加载数据库驱动(对应的Driver实现类中有注册驱动的静态代码块)
        Class.forName(driverClass);

        // 3.通过DriverManager的getConnection（）方法获取数据库链接
        Connection connection = DriverManager.getConnection(url, user, password);
        LOGGER.info(connection.toString());
    }

    @Test
    public void testDriver() throws SQLException {
        // 1.创建一个Driver实现类对象
        Driver driver = new com.mysql.jdbc.Driver();

        // 2.准备连接数据库的基本信息：url,user,password
        String url = "jdbc:mysql://192.168.100.66:3306/java_test";
        Properties properties = new Properties();
        properties.put("user", "crscd");
        properties.put("db.password", "crscd123@");

        // 3.调用Driver接口的connect(url, properties)获取数据库链接
        Connection connection = driver.connect(url, properties);
        System.out.println(connection);
    }

    /**
     * 编写一个通用的方法，在不修改源程序的情况下，可以获取任何数据库的链接
     * 解决方案：把数据库驱动Driver实现类的全类名、url、user、password放入一个
     * 配置文件中，通过修改配置文件的方式实现和具体数据库解耦
     */
    public Connection getConnection() throws ClassNotFoundException, IOException, IllegalAccessException, InstantiationException, SQLException {
        InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        Properties property = new Properties();
        property.load(in);
        String driverClass = property.getProperty("db.driver");
        String url = property.getProperty("db.url");
        String user = property.getProperty("user");
        String password = property.getProperty("db.password");

        Driver driver = (Driver) Class.forName(driverClass).newInstance();
        Properties properties = new Properties();
        properties.put("user", user);
        properties.put("db.password", password);

        return driver.connect(url, properties);
    }

    @Test
    public void testProperty() {
        try {
            Connection connection = getConnection();
            LOGGER.info(connection.toString());
        } catch (ClassNotFoundException | IllegalAccessException | InstantiationException | IOException | SQLException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnectionByManager() throws SQLException, IOException, ClassNotFoundException {
        // 1.1 创建Properties对象
        Properties property = new Properties();
        // 1.2 获取jdbc.properties对应的输入流
        InputStream in = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        // 1.3 加载2对应的输入流
        property.load(in);
        // 1.4 取出四个属性配置
        String driverClass = property.getProperty("db.driver");
        String url = property.getProperty("db.url");
        String user = property.getProperty("user");
        String password = property.getProperty("db.password");

        // 2.加载数据库驱动(对应的Driver实现类中有注册驱动的静态代码块)
        Class.forName(driverClass);

        // 3.通过DriverManager的getConnection（）方法获取数据库链接
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 取得数据库自动生成的主键
     */
    @Test
    public void testGetKeyValue() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO customers(name, email, birth) VALUES(?,?,?)";
            // 使用重载的prepareStatement(sql, flag)
            // 来生成PreparedStatement对象
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, "xiaoming");
            statement.setString(2, "x@123.com");
            statement.setString(3, "1983-03-09");
            int r = statement.executeUpdate();
            LOGGER.info("insert success?" + (r > 0));
            // 通过getGeneratedKeys()获取了包含了新生成主键的ResultSet对象
            // 在ResultSet中只有一列GENERATED_KEY，用于存放新生成的主键值
            ResultSet rs = statement.getGeneratedKeys();
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                LOGGER.info("column label:" + metaData.getColumnLabel(i + 1));
            }
            if (rs.next()) {
                LOGGER.info("auto gen key is " + rs.getObject(1));
            }

        } catch (Exception ex) {
            LOGGER.error("error", ex);
        } finally {
            JdbcUtils.release(connection, statement);
        }
    }

    /**
     * 插入BLOB类型的数据必须使用PreparedStatement,因为BLOB类型
     * 的数据是无法使用字符串拼写的。
     * <p>
     * 调用setBlob(int index, InputStream in)即可
     */
    @Test
    public void testInsertBlob() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO customers(name, email, birth, picture) VALUES(?,?,?,?)";
            // 使用重载的prepareStatement(sql, flag)
            // 来生成PreparedStatement对象
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, "xiaoming");
            statement.setString(2, "x@123.com");
            statement.setString(3, "1983-03-09");
            InputStream in = new FileInputStream("/home/lzy/Pictures/Wallpapers/pic0.jpg");
            statement.setBlob(4, in);
            int r = statement.executeUpdate();
            LOGGER.info("insert success?" + (r > 0));
            // 通过getGeneratedKeys()获取了包含了新生成主键的ResultSet对象
            // 在ResultSet中只有一列GENERATED_KEY，用于存放新生成的主键值
            ResultSet rs = statement.getGeneratedKeys();
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                LOGGER.info("column label:" + metaData.getColumnLabel(i + 1));
            }
            if (rs.next()) {
                LOGGER.info("auto gen key is " + rs.getObject(1));
            }

        } catch (Exception ex) {
            LOGGER.error("error", ex);
        } finally {
            JdbcUtils.release(connection, statement);
        }
    }

    /**
     * 读取BLOB数据：
     * 1.使用getBlob方法读取到Blob对象
     * 2.调用Blob的getBinaryStream()方法得到输入流，再使用IO操作即可
     */
    @Test
    public void testReadBlob() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //获取数据库customers表第一条记录并打印
            //1. 获取Connection
            connection = JdbcUtils.getConnection();
            //3. 准备SQL
            String sql = "SELECT id, name, email, birth, picture FROM customers WHERE id=?";
            //2. 获取Statement
            statement = connection.prepareStatement(sql);
            statement.setObject(1, 10);
            //4. 执行查询
            resultSet = statement.executeQuery();
            //5. 处理ResultSet
            if (resultSet.next()) {
                int id = resultSet.getInt(1);
                String name = resultSet.getString("name");
                String email = resultSet.getString(3);
                String birth = resultSet.getString(4);
                Blob pic = resultSet.getBlob(5);

                InputStream in = pic.getBinaryStream();
                OutputStream out = new FileOutputStream("/home/lzy/Pictures/Wallpapers/pic1.jpg");
                byte[] buffer = new byte[1024];
                int len = 0;
                while ((len = in.read(buffer)) != -1) {
                    out.write(buffer, 0, len);
                }
                in.close();
                out.close();
            }
        } catch (Exception e) {
            LOGGER.error("query error", e);
        } finally {
            //6.关闭数据库连接
            JdbcUtils.release(connection, statement, resultSet);
        }
    }

    /**
     * 向Oracle的customers表中插入10万行数据
     * 测试如何插入，用时最短
     * 1. 使用statement
     */
    @Test
    public void testBatchWithStatement() {
        Connection connection = null;
        Statement statement = null;
        String sql = null;
        try {
            connection = JdbcUtils.getConnection();
            JdbcUtils.beginTransaction(connection);
            statement = connection.createStatement();

            Instant start = Instant.now();

            for (int i = 0; i < 100000; i++) {
                sql = "INSERT INTO customers VALUES(" + (i + 1) + ",'name_" + (i + 1) + "','1995-03-04')";
                statement.executeUpdate(sql);
            }

            Instant end = Instant.now();
            // 117053ms
            LOGGER.info("insert 100000 data into oracle consume " + Duration.between(start, end).toMillis() + "ms");

            JdbcUtils.commit(connection);
        } catch (Exception e) {
            JdbcUtils.rollback(connection);
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement);
        }
    }

    @Test
    public void testBatchWithPreparedStatement() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            JdbcUtils.beginTransaction(connection);

            String sql = "INSERT INTO customers VALUES(?,?,?)";
            statement = connection.prepareStatement(sql);
            Instant start = Instant.now();

            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.setString(2, "test_" + i);
                statement.setString(3, "1990-09-09");
                statement.executeUpdate();
            }

            Instant end = Instant.now();
            // 52413ms
            LOGGER.info("insert 100000 data into oracle consume " + Duration.between(start, end).toMillis() + "ms");

            JdbcUtils.commit(connection);
        } catch (Exception e) {
            JdbcUtils.rollback(connection);
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement);
        }
    }

    @Test
    public void testBatchWithBatchStatement() {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = JdbcUtils.getConnection();
            JdbcUtils.beginTransaction(connection);

            String sql = "INSERT INTO customers VALUES(?,?,?)";
            statement = connection.prepareStatement(sql);
            Instant start = Instant.now();

            for (int i = 0; i < 100000; i++) {
                statement.setInt(1, i + 1);
                statement.setString(2, "test_" + i);
                statement.setString(3, "1990-09-09");

                // 积攒sql
                statement.addBatch();

                // 当积攒到一定程度，就统一执行一次，并且清空先前积攒的sql
                if ((i + 1) % 300 == 0) {
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }

            // 若总条数不是批量数值的整数倍，则需要额外执行一次，并且清空先前积攒的sql
            if (10000 % 300 != 0) {
                statement.executeBatch();
                statement.clearBatch();
            }

            Instant end = Instant.now();
            // 1046ms
            LOGGER.info("insert 100000 data into oracle consume " + Duration.between(start, end).toMillis() + "ms");

            JdbcUtils.commit(connection);
        } catch (Exception e) {
            JdbcUtils.rollback(connection);
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement);
        }
    }

    /**
     * 如何使用JDBC调用存储在数据库中的函数或存储过程
     */
    @Test
    public void testCallableStatement() {
        Connection connection = null;
        CallableStatement statement = null;

        try {
            connection = JdbcUtils.getConnection();
            // 1. 通过Connection对象的prepareCall()方法创建一个CallableStatement对象的实例
            // 需要传入一个String类型的字符串，该字符串用于指明如何调用存储过程
            String sql = "{?= call sum_salary(?,?)}";
            statement = connection.prepareCall(sql);
            // 2. 通过CallableStatement对象的registerOutParameter()方法注册OUT参数
            //　若想设置为设置为null，可以使用setNull()方法
            statement.registerOutParameter(1, Types.NUMERIC);
            statement.registerOutParameter(3, Types.NUMERIC);
            // 3. 通过CallableStatement对象的setXxx()方法设定IN 或IN OUT参数
            statement.setInt(2, 8);
            // 4. 通过CallableStatement对象的execute方法执行存储过程
            statement.execute();
            // 5. 如果所调用的是带有返回值的存储过程，还需要通过CallableStatement对象的get方法获取返回值
            double sumSalary = statement.getDouble(1);
            long empCount = statement.getLong(3);

            LOGGER.info(String.valueOf(sumSalary));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement);
        }

    }
}
